#coding=utf-8

import string
import re
import copy

import MySQLdb as mysql
from MySQLdb import cursors
from func_ext import list2str

class database(object) :
    """ 数据库操作类 """
    dbMapKey = 'db_map'    #数据库映射项key
    hasDBMap = False    #是否做连接映射
    dbKeyParams = {}    #数据库连接配置项参数
    dbConnectParams = {}    #数据库连接参数
    defaultCursor = cursors.DictCursor    #默认游标类
    conn = None     #数据库连接对象
    cursor = None   #数据库游标

    def __init__(self,setting,dbKey='db') :
        """ 初始化操作 """
        self.dbKey = dbKey
        self.setting = setting
        self.getDBMapParams()
        self._connect()

    def _connect(self) :
        """""""""""""""""""""""""""""""""""""""
         # 连接数据库
         #
         # @author   alone <phperlon@gmail.com>
         # @param    
         # @return   
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        try :
            if not self.conn :
                self.conn = mysql.connect(**self.dbConnectParams)
                self.conn.autocommit(True)
        except mysql.Error,e :
            self._mysqlError(e)
            return False
        try :
            #如配置未指定游标类 则使用默认指定的游标类 self.defaultCursor
            self.dbCursor = self.dbConnectParams.get('cursorclass')
            if not self.dbCursor :
                self.cursor = self.conn.cursor(self.defaultCursor)
            else :
                self.cursor = self.conn.cursor()
        except mysql.Error,e :
            self._mysqlError(e)
            return False
        
        #设置mysql连接字符集
        charset = self.dbConnectParams.get('charset')
        self.setCharset(charset)
        return self.conn

    def setCharset(self,charset='') :
        """""""""""""""""""""""""""""""""""""""
         # 设置连接字符集
         #
         # @author   alone <phperlon@gmail.com>
         # @param    
         # @return   
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        charset = charset and charset or 'utf8'
        self.query('SET NAMES "%s"' % charset)

    def getDBKeyParams(self) :
        """""""""""""""""""""""""""""""""""""""
         #
         # 获取dbKey配置参数
         #
         # @author   alone <phperlon@gmail.com>
         # @param    void
         # @return   dict
         # @todo     
         #
        """""""""""""""""""""""""""""""""""""""
        #检测配置文件中 是否含有dbKey配置项
        dbKeyItems = self.setting.get(self.dbKey)
        if dbKeyItems :
            self.dbKeyParams = dbKeyItems
        return self.dbKeyParams

    def getDBMapParams(self) :
        """""""""""""""""""""""""""""""""""""""
         # 获取数据库连接映射配置参数
         #
         # @author   alone <phperlon@gmail.com>
         # @param    object cf
         # @return   dict
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        self.getDBKeyParams()
        #判断有没有数据库映射参数关键字
        self.hasDBMap = self.dbMapKey in self.setting
        if self.hasDBMap :
            dbMapItems = self.setting.get(self.dbMapKey,{})
        else :
            dbMapItems = self.getDefaultDBMap()

        for k,v in dbMapItems.iteritems() :
            value = self.dbKeyParams.get(v)
            if value :
                self.dbConnectParams[k] = value
        self.dbConnectParams['port'] = int(self.dbConnectParams.get('port',3306))
        return self.dbConnectParams

    def getDefaultDBMap(self) :
        """""""""""""""""""""""""""""""""""""""
         # 获取默认映射参数
         #
         # @author   alone <phperlon@gmail.com>
         # @param    void
         # @return   list
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        dbDefaultMap = {
                    'host':'db_host',
                    'port':'db_port',
                    'user':'db_user',
                    'passwd':'db_passwd',
                    'db':'db_name',
                    'charset':'db_charset',
                    'unix_socket':'db_socket',
                    'cursorclass':'db_cursor',
                    'conv':'db_conv',
                    'ssl':'db_ssl',
                    'named_pipe':'db_pipe',
                    'sql_mode':'db_sql_mode',
                    'connect_timeout':'db_timeout',
                    'compress':'db_compress',
                    'init_command':'db_init_command',
                    'default_file':'db_default_file',
                    'default_group':'db_default_group',
                    'use_unicode':'db_use_unicode',
                    'client_flag':'db_client_flag',
                    'local_infile':'db_local_infile',
                 }
        return dbDefaultMap

    def query(self,sql,cursor=None) :
        """""""""""""""""""""""""""""""""""""""
         # 执行一条sql查询语句
         #
         # @author   alone <phperlon@gmail.com>
         # @param    string sql
         # @param    object cursor
         # @return   list
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        if not cursor and not self.cursor:
            self._connect()
        self.cursor.execute(sql)
        return {'cursor':self.cursor,'sql':sql}

    def select(self,sql) :
        """""""""""""""""""""""""""""""""""""""
         # 获取所有记录
         #
         # @author   alone <phperlon@gmail.com>
         # @param    string sql
         # @return   tuple
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        self.query(sql)
        return self.cursor.fetchall()

    def selectRow(self,sql) :
        """""""""""""""""""""""""""""""""""""""
         # 获取一条记录
         #
         # @author   alone <phperlon@gmail.com>
         # @param    string sql
         # @return   dict
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        self.query(sql)
        return self.cursor.fetchone()
        #return self.selectLimit(sql,1)

    def selectLimit(self,sql,limit=20,offset=0) :
        """""""""""""""""""""""""""""""""""""""
         # 获取指定数目的记录
         #
         # @author   alone <phperlon@gmail.com>
         # @param    string sql
         # @return   tuple
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        if limit >= 0 or offset >= 0 :
            offset = (offset >= 0) and (str(offset)+",") or ''
            limit = (limit >= 0) and limit or 18446744073709551615
        sql += ' LIMIT ' + offset + '' + str(limit)
        return self.select(sql)

    def selectMany(self,sql,num) :
        """""""""""""""""""""""""""""""""""""""
         # 获取多条记录
         #
         # @author   alone <phperlon@gmail.com>
         # @param    string sql
         # @return   tuple
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        self.query(sql)
        return self.cursor.fetchmany(num)
        #return self.selectLimit(sql,limit=num)

    def rowCount(self) :
        """""""""""""""""""""""""""""""""""""""
         # 获取结果集中行的数目
         #
         # @author   alone <phperlon@gmail.com>
         # @param    
         # @return   
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        return self.cursor.rowcount

    def batchExec(self,sql,args) :
        """""""""""""""""""""""""""""""""""""""
         # 批量执行sql语句
         #
         # @author   alone <phperlon@gmail.com>
         # @param    string sql [eg. 'INSERT INTO example(`name`,`value`)VALUES(%s,%s)']
         # @param    list/tuple args [eg. [(1,2),(3,4),(5,6)]]
         # @return   int 查询所影响的行数
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        if not self.cursor:
            self._connect()
        return self.cursor.executemany(sql,args)

    def getBatchInsertSql(self,table,fields) :
        """""""""""""""""""""""""""""""""""""""
         # 获取批量插入格式化sql语句
         #
         # @author   alone <phperlon@gmail.com>
         # @param    string table 表名
         # @param    list fields 插入字段列表
         # @return   string formatSql
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        formatSql = 'INSERT INTO '+table
        keys = list2str(fields)
        formatSql += '(`'+string.join(keys,'`,`')+'`)'
        repeat_format = string.strip('%s,'*len(keys),',')
        formatSql += 'VALUES('+repeat_format+')'
        return formatSql

    def getInsertSql(self,rs,data) :
        """""""""""""""""""""""""""""""""""""""
         # 生成插入sql语句
         #
         # @author   alone <phperlon@gmail.com>
         # @param    object cursor 游标对象
         # @param    dict data 插入的数据字典
         # @return   string sql
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        if not rs :
            print 'GetInsertSQL: '+rs.get('sql')+' error '
            return False
        tableName = self.getSqlTable(rs.get('sql',''))
        tmp = {}
        for k,v in data.iteritems() :
            tmp[k.lower()] = v

        insertValue = {}
        insField = []
        insValue = []
        fieldInfo = self.getFieldInfo(rs.get('cursor'))
        for field in fieldInfo :
            if field in tmp :
                #insertValue[field] = self.quoteValue(tmp[field])
                insField.append(self.backQuote(field))
                insValue.append(self.quoteValue(tmp[field]))
        strField = ','.join(insField)
        strValue = ','.join(insValue)
        sql = 'INSERT INTO %s(%s)VALUES(%s)' % (tableName,strField,strValue)
        return sql

    def getSqlTable(self,sql) :
        """""""""""""""""""""""""""""""""""""""
         # 获取sql语句中的表名称
         #
         # @author   alone <phperlon@gmail.com>
         # @param    
         # @return   void
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        table = False
        rs = re.search(r'FROM\s+([]0-9a-z_:"`.@[-]*)', sql,re.I)
        if rs :
            table = rs.group(1)
        return table

    def getFieldInfo(self,cursor,toLower=True) :
        """""""""""""""""""""""""""""""""""""""
         # 获取游标对象结果集中的字段信息
         #
         # @author   alone <phperlon@gmail.com>
         # @param    object cursor
         # @return   list
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        fieldInfo = []
        #cursor.description获取字段的详细信息 参见(http://www.python.org/dev/peps/pep-0249/)
        for v in cursor.description :
            field = toLower and v[0].lower() or v[0]
            fieldInfo.append(field)
        return fieldInfo

    def _mysqlError(self,e) :
        """""""""""""""""""""""""""""""""""""""
         # mysql错误信息
         #
         # @author   alone <phperlon@gmail.com>
         # @param    object e
         # @return   void
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        print 'Mysql Error:'
        print e

    def quoteValue(self,value) :
        return '"%s"' % str(value)

    def backQuote(self,value) :
        return '`%s`' % str(value)

    def _getFilterType(self,type,value) :
        """""""""""""""""""""""""""""""""""""""
         # 根据类型获取过滤值
         #
         # @author   alone <phperlon@gmail.com>
         # @param    
         # @return   void
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        '''
        if isinstance(var,list) :
            value = map(lambda x:self.quoteValue(x),var)
        else :
            value = self.quoteValue(var)

        if type == 'than' :
            mapValue = ' > '+value
        elif type == 'lthan' :
            mapValue = ' < '+value
        elif type == 'nequal' :
            mapValue = ' = '+value
        elif type == 'noequal' :
            mapValue = ' != '+value
        elif type == 'tequal' :
            mapValue = ' = '+value
        elif type == 'sthan' :
            mapValue = ' <= '+value
        elif type == 'bthan' :
            mapValue = ' >= '+value
        elif type == 'has' :
            mapValue = ' LIKE "%'+value+'%"'
        elif type == 'head' :
            mapValue = ' LIKE \"'+value+'%\"'
        elif type == 'foot' :
            mapValue = ' LIKE \"%'+value+'\"'
        elif type == 'nohas' :
            mapValue = ' NOT LIKE "%'+value+'%"'
        elif type == 'between' :
            mapValue = ' {field}>='+value[0]+' AND '+' {field}<'+value[1]
        elif type == 'in' :
            mapValue = ' IN ("'+'","'.join(value)+'") '

        return mapValue
        '''

        filterMap = {}
        if isinstance(value,bool) :
            value = value and 'true' or 'false'

        if isinstance(value,list) :
            value = list2str(value)
            filterMap = {
                'between':' {field}>=%s AND {field}<%s' % (value[0],value[1]),
                'in':' in ("'+'","'.join(list(value))+'") ',
            }
        elif isinstance(value,(basestring,int,float)) :
            filterMap = {
                'than':' > "%s"' % value,
                'gt':' > "%s"' % value,
                'lthan':' < "%s"' % value,
                'lt':' < "%s"' % value,
                'nequal':' = "%s"' % value,
                'eq':' = "%s"' % value,
                'noequal':' != "%s"' % value,
                'neq':' != "%s"' % value,
                'tequal':' = "%s"' % value,
                'eq':' = "%s"' % value,
                'sthan':' <= "%s"' % value,
                'lte':' <= "%s"' % value,
                'bthan':' >= "%s"' % value,
                'gte':' >= "%s"' % value,
                'has':' LIKE "%%%s%%"' % value,
                'head':' LIKE "%s%%"' % value,
                'foot':' LIKE "%%%s"' % value,
                'nohas':' NOT LIKE "%%%s%%"' % value,
            }
        return filterMap.get(type,'')

    def _filter(self,filter,tableAlias='',baseWhere=[]) :
        """""""""""""""""""""""""""""""""""""""
         # 过滤筛选条件生成where子句
         #
         # @author   alone <phperlon@gmail.com>
         # @param    dict filter
         # @param    string tableAlias
         # @param    list baseWhere
         # @return   string
         # @todo     
        """""""""""""""""""""""""""""""""""""""
        where = baseWhere and baseWhere or ['1']
        alias = tableAlias and (tableAlias+'.') or ''

        for key,var in filter.iteritems() :
            if (key.find('|') != -1) :
                tmp = key.split('|')
                field = tmp[0]
                type = tmp[1]
            else :
                field = key
                type = 'tequal'
            _str = self._getFilterType(type,var)
            if (_str.find('{field}') != -1) :
                where.append(_str.replace('{field}',alias+'`'+field+'`'))
            else :
                where.append(alias+'`'+field+'`'+_str)
        return ' AND '.join(where)